<?php

namespace App\Model;


use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class OutAnItemStatistics extends BaseModel
{
    protected $connection = 'mysql';
    protected $table = 'out_an_item_statistics';
    protected $fillable = ['filed_id','value','created_date','total'];
    
    public function getList(Request $request){
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);
        $page = ($page - 1) * $limit;
        $begin_date = $request->input('begin_date');
        $end_date = $request->input('end_date');
    
        $where = '';
        if($begin_date && $end_date) $where = "WHERE created_date BETWEEN '{$begin_date}' AND '{$end_date}'";
        if($begin_date && !$end_date) $where = "WHERE created_date >='{$begin_date}'";
        if(!$begin_date && $end_date) $where = "WHERE created_date <='{$end_date}'";
        
        $fieldsList = EnterAnItemStatisticsFields::where('type',1)->get();
        $total = DB::connection('mysql')->selectOne("SELECT COUNT(DISTINCT created_date) total FROM cms_out_an_item_statistics {$where}");
    
        // 初始化累加变量值
        DB::connection('mysql')->statement("SET @total :=0");
        
        $sql = "SELECT MAX(c.total) total,";
        foreach ($fieldsList as $field) {
            $sql .= "SUM(CASE WHEN c.filed_id={$field->id} THEN c.value ELSE 0 END) {$field->value},";
        }
        $sql .= "SUM(c.value) total_price,c.created_date FROM (
	SELECT
	filed_id,
	`value`,
	SUM(`value`) total_price,
	created_date,
	(@total := @total + `value`) total
FROM
	cms_out_an_item_statistics
GROUP BY
	id
        ) c {$where} GROUP BY c.created_date ORDER BY c.created_date DESC LIMIT {$page}, {$limit}";
        Log::debug($sql);
        $data = DB::connection('mysql')->select($sql);
        return [ 'data' => $data, 'total' => $total->total ?? 0];
    }
    
    public function createData(Request $request){
        return self::firstOrCreate($request->only(['filed_id','value','created_date']));
    }
    
    /**
     * 删除
     * @param string $created_date
     * @return int
     */
    public function destroyData(string $created_date){
        return self::where('created_date',$created_date)->delete();
    }
}
